1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to the shared computation of user-defined metrics for an on-line analytical processing (OLAP) system that uses a relational database management system (RDBMS).
2. Description of Related Art
On-Line Analytical Processing (OLAP) systems provide tools for analysis of multi-dimensional data. Most systems are built using a three-tier architecture, wherein the first or client tier provides a graphical user interface (GUI) or other application, the second or middle tier provides a multi-dimensional view of the data, and the third or server tier comprises a relational database management system (RDBMS) that stores the data.
Most queries in OLAP systems are complex and require the aggregation of large amounts of data. In OLAP systems, expressions and aggregates are often generated by user-defined metrics. Examples of such metrics include running averages of sales over vanous time windows, actual vs. forecast profit margins, and many others. Often, one metric is defined in terms of another, e.g., profit may be defined in terms of sales and various costs. Frequently, a single user query will request multiple metrics, and each metric may have many component sub-metrics. The result is a complex set of expressions and aggregates, which provide the potential for sharing and re-use during evaluation.
A naive way of executing these sets of expressions and aggregations is to consider each expression or aggregation in isolation, evaluating each one separately from all the others. In many cases, this naive approach is very inefficient, because the expressions and aggregations often share a lot of internal structure. Thus, there is a need in the art for improved techniques for evaluating expressions, in order to improve the performance of OLAP systems.
An On-Line Analytic Processing (OLAP) system computes complex expressions and aggregations in queries by re-using and sharing subparts of the expressions and aggregations. A dependency generation phase performed by the OLAP system identifies dependencies among metrics based on the expressions, aggregations, and other metrics used by the metrics. An access plan generation phase performed by the OLAP system generates an access plan based on the identified dependencies, wherein the access plan ensures that expressions, aggregations, and metrics are computed before they are needed, and that required values and intermediate results are passed up a tree structure of the access plan until they are used or consumed by some operator. An operator assignment phase performed by the OLAP system generates operators based on the access plan, and also generates project list expressions, aggregations to be computed in each operator, and input and output tuple types for each operator.